package by.scsbsu.database.dao;

import java.util.ArrayList;

import by.scsbsu.database.bean.Message;
import by.scsbsu.database.tools.DbConnectionPool;

public class MessageDAO extends AbstractDAO {
	public static final String SQL_MESSAGE_CREATE_MESSAGE = "INSERT INTO message ( date , is_read , id_user_from , id_user_to , title , text , id_message_parent ) VALUES ( ? , ? , ? , ? , ? , ? , ? );";
	public static final String SQL_MESSAGE_GET_ID_CREATE_MESSAGE = "SELECT id FROM message WHERE ( ( date = ? ) AND ( id_user_from = ? ) );";
	public static final String SQL_MESSAGE_DELETE_MESSAGE_BY_ID = "DELETE FROM message WHERE ( id = ? );";
	/**/public static final String SQL_MESSAGE_GET_LIST_OF_MESSAGE_BY_ID_USER_TO = "SELECT * FROM message WHERE ( ( id_user_to = ? ) AND ( is_read = ? ) );";
	/**/public static final String SQL_MESSAGE_UPDATE_IS_READ = "UPDATE message SET ( is_read = TRUE ) WHERE ( id = ? );";
	
	
	public Message createMessage(Message message){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_MESSAGE_CREATE_MESSAGE);
			statement.setLong(1, message.getDate());
			statement.setBoolean(2, message.isRead());
			statement.setInt(3, message.getIdUserFrom());
			statement.setInt(4, message.getIdUserTo());
			statement.setString(5, message.getTitle());
			statement.setString(6, message.getText());
			statement.setInt(7, message.getIdMessageParent());
			statement.executeUpdate();
			
			statement = connection.prepareStatement(SQL_MESSAGE_GET_ID_CREATE_MESSAGE);
			statement.setLong(1, message.getDate());
			statement.setInt(2, message.getIdUserFrom());
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				message.setId(resultSet.getInt("id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return message;
	}
	
	public ArrayList<Message> getListOfReadMessage(int id_user_to){
		ArrayList<Message> message = new ArrayList<Message>();
		Message mes = new Message();
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_MESSAGE_GET_LIST_OF_MESSAGE_BY_ID_USER_TO);
			statement.setInt(1, id_user_to);
			statement.setBoolean(2, true);
			
			resultSet = statement.executeQuery();
			while(resultSet.next()){
				mes.setId(resultSet.getInt("id"));
				mes.setDate(resultSet.getLong("date"));
				mes.setRead(true);
				mes.setIdUserFrom(resultSet.getInt("id_user_from"));
				mes.setIdUserTo(resultSet.getInt("id_user_to"));
				mes.setTitle(resultSet.getString("title"));
				mes.setText(resultSet.getString("text"));
				mes.setIdMessageParent(resultSet.getInt("id_message_parent"));
				message.add(mes);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return message;
	}
	
	public ArrayList<Message> getListOfNotReadMessage(int id_user_to){
		ArrayList<Message> message = new ArrayList<Message>();
		Message mes = new Message();
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_MESSAGE_GET_LIST_OF_MESSAGE_BY_ID_USER_TO);
			statement.setInt(1, id_user_to);
			statement.setBoolean(2, false);
			
			resultSet = statement.executeQuery();
			while(resultSet.next()){
				mes.setId(resultSet.getInt("id"));
				mes.setDate(resultSet.getLong("date"));
				mes.setRead(false);
				mes.setIdUserFrom(resultSet.getInt("id_user_from"));
				mes.setIdUserTo(resultSet.getInt("id_user_to"));
				mes.setTitle(resultSet.getString("title"));
				mes.setText(resultSet.getString("text"));
				mes.setIdMessageParent(resultSet.getInt("id_message_parent"));
				message.add(mes);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return message;
	}
	
	public void updateMessage(int id){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_MESSAGE_UPDATE_IS_READ);
			statement.setInt(1, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void deleteMessageById(int id){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_MESSAGE_DELETE_MESSAGE_BY_ID);
			statement.setInt(1, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
}
